import json,pymysql
# 准备插入的数据

data_list = []
with open(file='menus.json', mode='r', encoding='utf-8') as f:
    menus = json.load(f)
    # print(menus.get('data'))
    data = menus.get('data')
    for menu in data:
        # print(menu)
        value_list = []
        for value in menu.values():
            value_list.append(value)
        data_list.append(tuple(value_list))
# for data in data_list:
#     print(data)
# 连接数据库
conn = pymysql.Connect(
    # host='47.94.130.233',
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='homemall',
    charset='utf8',
)
# 获取游标
my_cursor = conn.cursor()
# 准备SQL
sql = 'insert into role_rule (authority_id,authority_name,order_number,menu_url,menu_icon,create_time,authority,checked,update_time,is_menu,parent_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

try:
    # 批量插入数据
    my_cursor.executemany(sql, data_list)
    # 手动提交事务
    conn.commit()
    print('提交了', my_cursor.rowcount, '条数据')
except Exception as e:
    print(e)
    # 事务回滚
    conn.rollback()
finally:
    # 关闭连接
    conn.close()
print('写入成功')
